﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using StockTrader.DAL.IDAL;
using StockTrader.DAL.MSSQLHelper;
using StockTrader.BusinessService.DataContract;

namespace StockTrader.DAL.SQLserver
{
    public class IndicatorCode : IIndicatorCode
    {
/*
                private SqlConnection _internalConnection;
                private SqlTransaction _internalADOTransaction = null;
        
                public void BeginADOTransaction()
                {
                    if (_internalConnection.State != System.Data.ConnectionState.Open)
                        _internalConnection.Open();
                    _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
                }
                public void RollBackTransaction()
                {
                    _internalADOTransaction.Rollback();
                    _internalADOTransaction = null;
                }
                public void CommitADOTransaction()
                {
                    _internalADOTransaction.Commit();
                    _internalADOTransaction = null;
                }
                public void Open(string connString)
                {
                    if (_internalConnection == null)
                        _internalConnection = new SqlConnection(connString);
                    if (_internalConnection.State != ConnectionState.Open)
                        _internalConnection.Open();
                }
                public void Close()
                {
                    if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                        _internalConnection.Close();
                }
        
                //SQL string
                private const string SQL_INDICATORCODE_INSERT = "INSERT INTO INDICATORCODE(NAME, CODE,DESCRIPTION, DOCTYPE, TYPE, PARRENT, POSITION, _LEVEL, FORMULAR) VALUES (@NAME, @CODE,@DESCRIPTION, @DOCTYPE, @TYPE, @PARRENT, @POSITION, @LEVEL, @FORMULAR); SELECT ID=@@IDENTITY;";
        
                private const string SQL_INDICATORCODE_DELETE = "DELETE FROM INDICATORCODE WHERE ID=@ID";
                private const string SQL_INDICATORCODE_UPDATE = "UPDATE INDICATORCODE SET NAME=@NAME, CODE=@CODE, DESCRIPTION = @DESCRIPTION, DOCTYPE=@DOCTYPE, TYPE=@TYPE, PARRENT=@PARRENT, POSITION=@POSITION, _LEVEL=@LEVEL, FORMULAR=@FORMULAR WHERE ID=@ID";
        
                private const string SQL_INDICATORCODE_GETALL = "SELECT * FROM V_INDICATORCODE";
                private const string SQL_INDICATORCODE_GETBYID = "SELECT * FROM V_INDICATORCODE WHERE ID=@ID";
                private const string SQL_INDICATORCODE_GETBYDOCTYPEANDLANG = "SELECT * FROM V_INDICATORCODE WHERE DOCTYPE=@DOCTYPE AND LANG=@LANG";
                private const string SQL_INDICATORCODE_GETBYDOCTYPEANDLANG_TOPLEVEL = "SELECT * FROM V_INDICATORCODE WHERE DOCTYPE=@DOCTYPE AND LANG=@LANG AND LEVEL=0";
                private const string SQL_INDICATORCODE_GETCHILDREN = "SELECT * FROM V_INDICATORCODE WHERE PARRENT=@ID";
        
                #region Parameters
                private const string PARM_ID = "@ID";
                private const string PARM_NAME = "@NAME";
                private const string PARM_CODE = "@CODE";
                private const string PARM_DESCRIPTION = "@DESCRIPTION";
                private const string PARM_DOCTYPE = "@DOCTYPE";
                private const string PARM_TYPE = "@TYPE";
                private const string PARM_PARRENT = "@PARRENT ";
                private const string PARM_POSITION = "@POSITION";
                private const string PARM_LEVEL = "@LEVEL";
                private const string PARM_FORMULAR = "@FORMULAR";
                private const string PARM_LANG = "@LANG";
        
                #endregion
        
                //Functions
                #region IIndicatorCode Members
        
        
                public int InsertIndicatorCode(IndicatorCodeData item)
                {
                    try
                    {
                        List<IndicatorCodeUI> codeList = new List<IndicatorCodeUI>();
                        SqlParameter[] parms = new SqlParameter[] 
                            {new SqlParameter(PARM_NAME, SqlDbType.NVarChar),
                             new SqlParameter(PARM_CODE, SqlDbType.NVarChar),
                             new SqlParameter(PARM_DESCRIPTION, SqlDbType.NText),
                             new SqlParameter(PARM_DOCTYPE, SqlDbType.Int),
                             new SqlParameter(PARM_TYPE, SqlDbType.Int),
                             new SqlParameter(PARM_PARRENT, SqlDbType.Int),
                             new SqlParameter(PARM_POSITION, SqlDbType.Int),
                             new SqlParameter(PARM_LEVEL, SqlDbType.Int),
                             new SqlParameter(PARM_FORMULAR, SqlDbType.NVarChar)};
                        parms[0].Value = item.Name;
                        parms[1].Value = item.Code;
                        parms[2].Value = item.Description;
                        parms[3].Value = item.DocType;
                        parms[4].Value = item.Type;
                        parms[5].Value = item.Parrent;
                        parms[6].Value = item.Position;
                        parms[7].Value = item.Level;
                        parms[8].Value = item.Formular;
                        return SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDICATORCODE_INSERT, parms);
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
        
                public void DeleteIndicatorCode(int Id)
                {
                    try
                    {
                        List<IndicatorCodeUI> codeList = new List<IndicatorCodeUI>();
                        SqlParameter parm1 = new SqlParameter(PARM_ID, SqlDbType.Int);
                        parm1.Value = Id;
                        SQLHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDICATORCODE_DELETE, parm1);
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
        
                }
        
                public void UpdateIndicatorCode(int id,string name, string code, string description, int doctype, int type, int parrent, int position, int level, string formular)
                {
                    try
                    {
                        List<IndicatorCodeUI> codeList = new List<IndicatorCodeUI>();
                        SqlParameter[] parms = new SqlParameter[] 
                            {new SqlParameter(PARM_NAME, SqlDbType.NVarChar),
                             new SqlParameter(PARM_CODE, SqlDbType.NVarChar),
                             new SqlParameter(PARM_DESCRIPTION, SqlDbType.NText),
                             new SqlParameter(PARM_DOCTYPE, SqlDbType.Int),
                             new SqlParameter(PARM_TYPE, SqlDbType.Int),
                             new SqlParameter(PARM_PARRENT, SqlDbType.Int),
                             new SqlParameter(PARM_POSITION, SqlDbType.Int),
                             new SqlParameter(PARM_LEVEL, SqlDbType.Int),
                             new SqlParameter(PARM_FORMULAR, SqlDbType.NVarChar),
                             new SqlParameter(PARM_ID, SqlDbType.Int)};
                        parms[0].Value = name;
                        parms[1].Value = code;
                        parms[2].Value = description;
                        parms[3].Value = doctype;
                        parms[4].Value = type;
                        parms[5].Value = parrent;
                        parms[6].Value = position;
                        parms[7].Value = level;
                        parms[8].Value = formular;
                        parms[9].Value = id;
                        SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDICATORCODE_UPDATE, parms);
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
        
        
                public List<IndicatorCodeUI> GetIndicatorCode()
                {
                    try
                    {
                        List<IndicatorCodeUI> codeList = new List<IndicatorCodeUI>();
                        SqlDataReader rdr = SQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDICATORCODE_GETALL);
                        while (rdr.Read())
                        {
                            IndicatorCodeUI item = new IndicatorCodeUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["CODE"].ToString(), rdr["DESCRIPTION"].ToString(), Convert.ToInt32(rdr["DOCTYPE"]), rdr["DOCTYPETEXT"].ToString(), Convert.ToInt32(rdr["TYPE"]), rdr["TYPETEXT"].ToString(), Convert.ToInt32(rdr["LANG"]), rdr["LANGTEXT"].ToString(), Convert.ToInt32(rdr["PARRENT"]), rdr["PARRENTTEXT"].ToString(), Convert.ToInt32(rdr["POSITION"]), Convert.ToInt32(rdr["LEVEL"]), rdr["FORMULAR"].ToString());
                            codeList.Add(item);
                        }
                        rdr.Close();
                        return codeList;
                    }
                    catch
                    {
                        throw;
                    }
                }
        
                public IndicatorCodeUI GetIndicatorCodeById(int id)
                {
                    try
                    {
                        SqlParameter parm1 = new SqlParameter(PARM_ID, SqlDbType.Int);
                        parm1.Value = id;
                        SqlDataReader rdr = SQLHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDICATORCODE_GETBYID, parm1);
                        if (rdr.HasRows)
                        {
                            rdr.Read();
                            IndicatorCodeUI item = new IndicatorCodeUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["CODE"].ToString(), rdr["DESCRIPTION"].ToString(), Convert.ToInt32(rdr["DOCTYPE"]), rdr["DOCTYPETEXT"].ToString(), Convert.ToInt32(rdr["TYPE"]), rdr["TYPETEXT"].ToString(), Convert.ToInt32(rdr["LANG"]), rdr["LANGTEXT"].ToString(), Convert.ToInt32(rdr["PARRENT"]), rdr["PARRENTTEXT"].ToString(), Convert.ToInt32(rdr["POSITION"]), Convert.ToInt32(rdr["LEVEL"]), rdr["FORMULAR"].ToString());
                            rdr.Close();
                            return item;
                        }
        
                        return null;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
        
                public List<IndicatorCodeUI> GetIndicatorCodeByLevelAndLang()
                {
                    throw new NotImplementedException();
                }
        
                public List<IndicatorCodeUI> GetIndicatorCodeByCodeAndLang()
                {
                    throw new NotImplementedException();
                }
        
                public List<IndicatorCodeUI> GetIndicatorCodeByDocTypeAndLang(int doctype, int lang)
                {
                    try
                    {
                        List<IndicatorCodeUI> codeList = new List<IndicatorCodeUI>();
                        SqlParameter[] parms = new SqlParameter[]{
                                new SqlParameter(PARM_DOCTYPE, SqlDbType.Int),
                                new SqlParameter(PARM_LANG, SqlDbType.Int)};
                        parms[0].Value = doctype;
                        parms[1].Value = lang;
                        SqlDataReader rdr = SQLHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDICATORCODE_GETBYDOCTYPEANDLANG, parms);
                        while (rdr.Read())
                        {
                            IndicatorCodeUI item = new IndicatorCodeUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["CODE"].ToString(), rdr["DESCRIPTION"].ToString(), Convert.ToInt32(rdr["DOCTYPE"]), rdr["DOCTYPETEXT"].ToString(), Convert.ToInt32(rdr["TYPE"]), rdr["TYPETEXT"].ToString(), Convert.ToInt32(rdr["LANG"]), rdr["LANGTEXT"].ToString(), Convert.ToInt32(rdr["PARRENT"]), rdr["PARRENTTEXT"].ToString(), Convert.ToInt32(rdr["POSITION"]), Convert.ToInt32(rdr["LEVEL"]), rdr["FORMULAR"].ToString());
                            codeList.Add(item);
                        }
                        rdr.Close();
                        return codeList;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
        
                public List<IndicatorCodeUI> GetTopLevelIndicatorCodeByDocTypeAndLang(int doctype, int lang)
                {
                    try
                    {
                        List<IndicatorCodeUI> codeList = new List<IndicatorCodeUI>();
                        SqlParameter[] parms = new SqlParameter[]{
                                new SqlParameter(PARM_DOCTYPE, SqlDbType.Int),
                                new SqlParameter(PARM_LANG, SqlDbType.Int)};
                        parms[0].Value = doctype;
                        parms[1].Value = lang;
                        SqlDataReader rdr = SQLHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDICATORCODE_GETBYDOCTYPEANDLANG_TOPLEVEL, parms);
                        while (rdr.Read())
                        {
                            IndicatorCodeUI item = new IndicatorCodeUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["CODE"].ToString(), rdr["DESCRIPTION"].ToString(), Convert.ToInt32(rdr["DOCTYPE"]), rdr["DOCTYPETEXT"].ToString(), Convert.ToInt32(rdr["TYPE"]), rdr["TYPETEXT"].ToString(), Convert.ToInt32(rdr["LANG"]), rdr["LANGTEXT"].ToString(), Convert.ToInt32(rdr["PARRENT"]), rdr["PARRENTTEXT"].ToString(), Convert.ToInt32(rdr["POSITION"]), Convert.ToInt32(rdr["LEVEL"]), rdr["FORMULAR"].ToString());
                            codeList.Add(item);
                        }
                        rdr.Close();
                        return codeList;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
        
                public List<IndicatorCodeUI> GetChildrenIndicatorCode(int id)
                {
                    try
                    {
                        List<IndicatorCodeUI> codeList = new List<IndicatorCodeUI>();
                        SqlParameter parm1 = new SqlParameter(PARM_ID, SqlDbType.Int);
                        parm1.Value = id;
                        SqlDataReader rdr = SQLHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDICATORCODE_GETCHILDREN, parm1);
                        while (rdr.Read())
                        {
                            IndicatorCodeUI item = new IndicatorCodeUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["CODE"].ToString(), rdr["DESCRIPTION"].ToString(), Convert.ToInt32(rdr["DOCTYPE"]), rdr["DOCTYPETEXT"].ToString(), Convert.ToInt32(rdr["TYPE"]), rdr["TYPETEXT"].ToString(), Convert.ToInt32(rdr["LANG"]), rdr["LANGTEXT"].ToString(), Convert.ToInt32(rdr["PARRENT"]), rdr["PARRENTTEXT"].ToString(), Convert.ToInt32(rdr["POSITION"]), Convert.ToInt32(rdr["LEVEL"]), rdr["FORMULAR"].ToString());
                            codeList.Add(item);
                        }
                        rdr.Close();
                        return codeList;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
        
                }
        
        
                #endregion*/
        
    }
}
